package ecom.appcode.dao.daointerface;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ecom.appcode.common.daobean.CategoryDaoBean;
import ecom.appcode.common.daobean.ShoesDaoBean;
import ecom.appcode.common.utility.DBUltils;

public class CategoryDaoImpl implements CategoryDao {
    /** load Category Name */
    public List<CategoryDaoBean> loadCategoryName() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblCategory";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<CategoryDaoBean> list = new ArrayList<CategoryDaoBean>();
            while (rs.next()) {
                /** Category ID */
                int categoryID = rs.getInt("categoryID");
                /** Category Name */
                String categoryName = rs.getString("categoryName");
                /** Description */
                String description = rs.getString("description");

                CategoryDaoBean category = new CategoryDaoBean(categoryID, categoryName, description);

                list.add(category);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Load Category by ID */
    public CategoryDaoBean loadCategoryByID(int ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblCategory WHERE categoryID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, ID);
            rs = stm.executeQuery();
            CategoryDaoBean category = new CategoryDaoBean();
            if (rs.next()) {
                /** Category ID */
                int categoryID = rs.getInt("categoryID");
                /** Category Name */
                String categoryName = rs.getString("categoryName");
                /** Description */
                String description = rs.getString("description");
                category = new CategoryDaoBean(categoryID, categoryName, description);
            }
            return category;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }
    
    /** Update Category */
    public boolean updateCategory(int categoryID, String categoryName, String description) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "UPDATE tblCategory SET categoryName = ?, description = ? WHERE categoryID = ?";
            stm = con.prepareStatement(sql);

            stm.setString(1, categoryName);
            stm.setString(2, description);
            stm.setInt(3, categoryID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    /** Add New Category */
    public boolean addCategory(String categoryName, String description){
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "INSERT INTO tblCategory VALUES(?,?)";
            stm = con.prepareStatement(sql);

            stm.setString(1, categoryName);
            stm.setString(2, description);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    
    /** Delete Category */
    public boolean deleteCategory(int categoryID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "DELETE FROM tblCategory WHERE categoryID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, categoryID);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
